﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace TableToExcel
{
    class Table_ToExcel
    {
        public Excel.Application m_xlApp = null;
        #region 外部接口
        /// <summary>将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制）
        /// 将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制）
        /// </summary>
        /// <param name="tempDataTable">数据源</param>
        /// <param name="PathFileName">保存excel的路径</param>
        /// <param name="ExcelRows">每一个Excel文件的行数</param>
        /// <param name="ExcelVersion">导出Excel的版本(2003,2007)</param>
        public void u_DataTableToExcel1(DataTable tempDataTable, string PathFileName, long ExcelRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            long rowNum = tempDataTable.Rows.Count;//导出数据的行数   
            int columnNum = tempDataTable.Columns.Count;//导出数据的列数   
            string sFileName = "";
            if (rowNum > ExcelRows)
            {
                long excelRows = ExcelRows;//定义个excel文件显示的行数，最大的行数为65535，不能超过65535                     
                int scount = (int)(rowNum / excelRows);//生成excel文件的个数   
                if (scount * excelRows < rowNum)//当总行数不被excelRows整除时，经过四舍五入可能excel的个数不准   
                {
                    scount = scount + 1;
                }
                for (int sc = 1; sc <= scount; sc++)
                {
                    int init = int.Parse(((sc - 1) * excelRows).ToString());
                    sFileName = PathFileName + sc.ToString();
                    long start = init;
                    long end = sc * excelRows - 1;
                    if (sc == scount)
                        end = rowNum - 1;
                    u_OutExcel(tempDataTable, start, end, sFileName, ExcelVersion);
                }
            }
            else
            {
                u_OutExcel(tempDataTable, 0, rowNum - 1, PathFileName, ExcelVersion);
            }
        }
        /// <summary>将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制)
        /// 将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制)
        /// </summary>
        /// <param name="tempDataTable">数据源</param>
        /// <param name="PathFileName">导出excel的路径</param>
        /// <param name="SheetRows">excel的文件中sheet的行数</param>
        /// <param name="ExcelVersion">导出Excel的版本</param>
        public void u_DataTableToExcel2(DataTable tempDataTable, string PathFileName, long SheetRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            long rowNum = tempDataTable.Rows.Count;//行数   
            int columnNum = tempDataTable.Columns.Count;//列数   
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示   
            m_xlApp.Visible = false;
            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            try
            {
                if (rowNum > SheetRows)//单张Sheet表格最大行数   
                {
                    long sheetRows = SheetRows;//定义每页显示的行数,行数必须小于65536   
                    int scount = (int)(rowNum / sheetRows);//导出数据生成的表单数   
                    if (scount * sheetRows < rowNum)//当总行数不被sheetRows整除时，经过四舍五入可能页数不准   
                    {
                        scount = scount + 1;
                    }
                    for (int sc = 1; sc <= scount; sc++)
                    {
                        if (sc > 1)
                        {
                            object missing = System.Reflection.Missing.Value;
                            worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet   
                        }
                        else
                        {
                            worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1   
                        }
                        string[,] datas = new string[sheetRows + 1, columnNum];

                        for (int i = 0; i < columnNum; i++) //写入字段   
                        {
                            datas[0, i] = tempDataTable.Columns[i].Caption;//表头信息   
                        }
                        Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                        range.Interior.ColorIndex = 15;//15代表灰色   
                        range.Font.Bold = true;
                        range.Font.Size = 9;
                        int init = int.Parse(((sc - 1) * sheetRows).ToString());
                        int r = 0;
                        int index = 0;
                        int result;
                        if (sheetRows * sc >= rowNum)
                        {
                            result = (int)rowNum;
                        }
                        else
                        {
                            result = int.Parse((sheetRows * sc).ToString());
                        }
                        for (r = init; r < result; r++)
                        {
                            index = index + 1;
                            for (int i = 0; i < columnNum; i++)
                            {
                                object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
                                datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式   
                            }
                            System.Windows.Forms.Application.DoEvents();
                            //添加进度条   
                        }
                        Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
                        fchR.Value2 = datas;
                        worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。   
                        m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化   
                        range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
                        //range.Interior.ColorIndex = 15;//15代表灰色   
                        range.Font.Size = 9;
                        range.RowHeight = 14.25;
                        range.Borders.LineStyle = 1;
                        range.HorizontalAlignment = 1;
                    }
                }
                else
                {
                    string[,] datas = new string[rowNum + 1, columnNum];
                    for (int i = 0; i < columnNum; i++) //写入字段   
                    {
                        datas[0, i] = tempDataTable.Columns[i].Caption;
                    }
                    Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                    range.Interior.ColorIndex = 15;//15代表灰色   
                    range.Font.Bold = true;
                    range.Font.Size = 9;
                    int r = 0;
                    for (r = 0; r < rowNum; r++)
                    {
                        for (int i = 0; i < columnNum; i++)
                        {
                            object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
                            datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式   
                        }
                        System.Windows.Forms.Application.DoEvents();
                        //添加进度条   
                    }
                    Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
                    fchR.Value2 = datas;
                    worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。   
                    m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
                    range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
                    //range.Interior.ColorIndex = 15;//15代表灰色   
                    range.Font.Size = 9;
                    range.RowHeight = 14.25;
                    range.Borders.LineStyle = 1;
                    range.HorizontalAlignment = 1;
                }
                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        string s2003 = PathFileName + ".xls";
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
                        break;
                    case "2007":
                        string s2007 = PathFileName + ".xlsx";
                        workbook.SaveCopyAs(s2007);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
            }
        }
        #endregion
        #region 内部接口
        //作用将dt的（startindex到endindex的数据导出到filename）---用于将海量数据导出到多个excel文件
        private void u_OutExcel(DataTable dt, long startindex, long endindex, string filename, string ExcelVersion)
         {
             try
             {
                 long columnNum = dt.Columns.Count;
                 long excelRows = endindex - startindex-1;
                 Excel.Application m_xlApp = new Excel.Application();
                 m_xlApp.DisplayAlerts = false;//不显示更改提示   
                 m_xlApp.Visible = false;
                 Excel.Workbooks workbooks = m_xlApp.Workbooks;
                 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  
                 string[,] datas = new string[excelRows + 1, columnNum];
                 for (int i = 0; i < columnNum; i++) //写入表头字段   
                 {
                     string sTitle = dt.Columns[i].ColumnName;
                     datas[0, i] = sTitle;//表头信息   
                 }
                 Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                 range.Interior.ColorIndex = 15;//15代表灰色   
                 range.Font.Bold = true;
                 range.Font.Size = 9;
                 int r = 0;
                 int row = 0;
                 for (r = Convert.ToInt32(startindex); r <= endindex; r++)
                 {
                     row++;
                     for (int i = 0; i < columnNum; i++)
                     {
                         string sname = dt.Columns[i].ToString().Trim();
                         object obj = dt.Rows[r][sname];
                         datas[row, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式   
                     }
                     System.Windows.Forms.Application.DoEvents();
                     //添加进度条   
                 }
                 Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
                 fchR.Value2 = datas;
                 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。   
                 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
                 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
                 //range.Interior.ColorIndex = 15;//15代表灰色   
                 range.Font.Size = 9;
                 range.RowHeight = 14.25;
                 range.Borders.LineStyle = 1;//1边框为实线 0为excel样式
                range.HorizontalAlignment = 1;
                 workbook.Saved = true;
                 switch (ExcelVersion)
                 {
                     case "2003":
                         string s2003 = filename + ".xls";
                         object ob = System.Reflection.Missing.Value;
                         workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
                         break;
                     case "2007":
                         string s2007 = filename + ".xlsx";
                         workbook.SaveCopyAs(s2007);
                         break;
                     default: break;
                 }
                 KillProcess("EXCEL");//杀死excel进程
            }
            catch (Exception ex)
            {
				MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

        }
        //关闭进程 
        private void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            try
            {
                foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName))
                {
                    thisproc.Kill();
                }
            }
            catch
            {

            }
        }
        #endregion
        #region 附调用方法
        //Table_ToExcel outtoexcel = new Table_ToExcel();
        //SaveFileDialog sfd = new SaveFileDialog();
        //sfd.Filter = "Excel File|*.xlsx";
        //sfd.FileName = DateTime.Now.ToString("yyyy.MM.dd HH.MM.SS");
        //DataTable dt = gridControl1.DataSource as DataTable;
        //DialogResult dd = sfd.ShowDialog(this);
        //if (dd == DialogResult.OK)
        //{
        //    outtoexcel.u_DataTableToExcel1(dt, sfd.FileName, 100, "2003");
        //}
        #endregion
    }

	class DataSetToExcel
	{
		public bool WriteToExcel(string filePath, DataSet ds)
		{
			Excel._Workbook	oWB;
			Excel._Worksheet oSheet;
			Excel.Range oRng;

			object oMissing = System.Reflection.Missing.Value;
			//Start Excel and get Application object.
			Excel.Application	oXL = new Excel.Application();
			oXL.Visible = false;

			try
			{
				//Get a new workbook.
				oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing,
					oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing));

				oXL.DisplayAlerts = false;
				for (int i = 0; i < ds.Tables.Count; i++)
				{
					oSheet = (Excel.Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing);
					oSheet.Name = ds.Tables[i].TableName.Trim();

					int rowIndex = 1;
					int colIndex = 0;

					for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
					{
						//Add table headers going cell by cell.
						oSheet.Cells[1, j + 1] = ds.Tables[i].Columns[j].ColumnName.Trim();
						colIndex++;

					}
					string maxHeaderCell = NumberToChar(colIndex) + "1";
					//Format A1:D1 as bold, vertical alignment = center.
					oSheet.get_Range("A1", maxHeaderCell).Font.Bold = true;
					oSheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
					// Create an array to multiple values at once.
					string[,] saNames = new string[ds.Tables[i].Rows.Count, ds.Tables[i].Columns.Count];

					for (int n = 0; n < ds.Tables[i].Rows.Count; n++)
					{
						for (int m = 0; m < ds.Tables[i].Columns.Count; m++)
						{
							saNames[n, m] = ds.Tables[i].Rows[n][ds.Tables[i].Columns[m].ColumnName.Trim()].ToString();
						}
						rowIndex++;
					}

					oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames;
					oRng = oSheet.get_Range("A1", maxHeaderCell);
					oRng.EntireColumn.AutoFit();

				}

				// 保存并关闭这个工作簿
				oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

				//Make sure Excel is visible and give the user control
				//of Microsoft Excel's lifetime.
				//oXL.Visible = true;
				//oXL.UserControl = true;

				return true;
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
				return false;
			}
			finally
			{
				oXL.Quit();             //釋放Excel資源 
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
				oWB = null;
				oSheet = null;
				oRng = null;
				oXL = null;
				GC.Collect();
			}
		}
		private string NumberToChar(int number)
		{
			if (1 <= number && 26 >= number)
			{
				int num = number + 64;
				System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
				byte[] btNumber = new byte[] { (byte)num };
				return asciiEncoding.GetString(btNumber);
			}
			else if (number > 26)
			{
				int NewNum = number % 26;
				int count = number / 26;
				string ss = NumberToChar(count);
				int num = NewNum + 64;
				System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
				byte[] btNumber = new byte[] { (byte)num };
				return ss + asciiEncoding.GetString(btNumber);
			}
			return "数字不在转换范围内";
		}
	}
}
namespace ExcelToTable
{
    class Excel_ToTable
    {
　　　　public DataTable CreateDataFromExcel(string    strPath)
　　　　{
			DataTable retDT = null;
			String strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
			strConn += strPath;
			strConn += @";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";
			OleDbConnection conn = null;
			OleDbCommand cmd = null;
			try
			{
				conn = new OleDbConnection(strConn);
				conn.Open();

				string strSheet = String.Empty;
				DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
				List<String> sheetList = new List<string>();
				foreach (DataRow row in tblSchema.Rows)
				{
					strSheet = (string)row["TABLE_NAME"];
					sheetList.Add(strSheet);
				}
				foreach (String str1 in sheetList)
				{
					string Sql = @"SELECT * FROM [";
					Sql += str1;
					Sql += @"]";
					cmd = new OleDbCommand(Sql, conn);
					OleDbDataAdapter dpa = new OleDbDataAdapter(cmd);
					DataSet Des = new DataSet();
					dpa.Fill(Des);
					foreach (DataTable mydt in Des.Tables)
					{
						if (retDT == null){
							retDT = new DataTable();
							DataRow dr = mydt.Rows[0];
							foreach (object myobj in dr.ItemArray)
							{
								retDT.Columns.Add(myobj.ToString());
							}
						}
						if(retDT != null){
							foreach (DataRow dr in mydt.Rows)
							{
								if(dr[0].ToString().Contains(@"Bar")){

								}
								else {
									DataRow mydr = retDT.NewRow();
									mydr.ItemArray = dr.ItemArray;
									retDT.Rows.Add(mydr);
								}
							}
						}
					}
					cmd.Dispose();
					dpa.Dispose();
					cmd = null;
				}
			}
			catch (System.Exception ex)
			{
				MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
			}
			finally
			{
				if(cmd != null)
					cmd.Dispose();
				if(conn != null)
					conn.Close();
			}
			return retDT;
		}
    }
}
